package com.eascs.web.o2o.scheduler.service;

import com.alibaba.dubbo.config.annotation.Reference;
import com.eascs.admin.o2o.badloan.service.IICancelService;
import com.eascs.admin.o2o.loan.entity.O2oLoanapplyEntity;
import com.eascs.app.dao.JdbcSession;
import com.eascs.app.dao.PageResult;
import com.eascs.app.dao.SqlPageQuery;
import com.eascs.app.log.Logger;
import com.eascs.app.log.LoggerFactory;
import com.eascs.common.util.StopWatch;
import com.eascs.common.util.StringUtil;
import com.eascs.web.o2o.dc.dao.WorkFlowDao;
import com.eascs.web.o2o.scheduler.dao.BaseDao;
import com.eascs.web.o2o.scheduler.dao.CrmDao;
import com.eascs.web.o2o.scheduler.dao.ServiceDao;
import com.eascs.web.o2o.scheduler.entity.CreditManagerEntity;
import com.eascs.web.o2o.scheduler.entity.EabaCustomerNew;
import com.eascs.web.o2o.scheduler.entity.O2oLoanUser;
import com.eascs.web.o2o.scheduler.entity.O2oPlatformRegionalEntity;
import com.eascs.web.o2o.scheduler.entity.RemouldCustomerBasicInfo;
import com.eascs.web.o2o.scheduler.filter.AssetTrans;
import com.eascs.web.o2o.scheduler.util.Constant;
import com.eascs.workflow.common.entity.sys.PlatformRegional;
import com.eascs.workflow.common.service.sys.SysOrgService;
import com.eascs.workflow.common.vo.ModelResult;
import org.springframework.beans.BeanUtils;
import org.springframework.stereotype.Service;

import javax.inject.Inject;
import java.util.Date;
import java.util.List;

@Service
public class SynchroService {

	protected final Logger logger = LoggerFactory.getLogger(getClass());

	@Inject
	ServiceService serviceService;
	@Reference
	SysOrgService sysOrgService;
	@Inject
	WorkFlowDao workFlowDao;
	@Inject
	ServiceDao serviceDao;
	@Inject
	BaseDao  baseDao;
	@Inject
	CrmDao  crmDao;

	@Inject
	JdbcSession jdbcSessionForService;
	@Inject
	JdbcSession jdbcSessionForOracle;
	@Reference
	IICancelService iicancelService;
	/**
	 * 信贷经理放款清单（同步wofkflow中的信贷经理数据到service库中）
	 * @param requiredType
	 * @param <T>
	 */
	public <T>  void creditManager(Class<T> requiredType) {
		StopWatch stopWatch = new StopWatch();
		String sql  = "SELECT ru.user_name, ru.role_name, r.province_name, o.org_name, o.unit_code,  " +
				" o.unit_no, ru.org_id, ru.user_no , e.is_dimission, e.join_date FROM " +
				"(SELECT * FROM act_org_role_user ru WHERE ru.role_id='lletjcsdd4evd' GROUP BY ru.user_no) ru " +
				"LEFT JOIN o2o_employee e ON ru.user_no=e.employee_no " +
				"LEFT JOIN act_org o ON ru.org_id = o.id " +
				"LEFT JOIN o2o_platform_regional r ON o.unit_code = r.platform_code ";
		logger.info("*******************[SERVICE]信贷经理放款清单同步数据开始*******************");

		String delSql = " DELETE FROM credit_manager ";
		jdbcSessionForService.execute(delSql);

		int index = 0;
		while (index >= 0) {
			SqlPageQuery<T> sqlPageQuery = new SqlPageQuery<>(requiredType, Constant.PROCESS_DATA_LIMIT, index, sql);
			PageResult<T>   pageResult   = this.workFlowDao.findDataWithPage(sqlPageQuery);
			if (pageResult.getReturnDataSize() == 0) return;
			for (int i = 0; i < pageResult.getReturnDataSize(); i++) {
				CreditManagerEntity obj = (CreditManagerEntity)pageResult.getData().get(i);
				try {
					boolean flag=jdbcSessionForService.insert(obj)>0;
					if(!flag){
						jdbcSessionForService.updateById(obj);
					}
				} catch (Exception e) {
					logger.error("数据库异常", e);
				}
			}
			if (pageResult.getPageTotal() > index + 1) {
				index++;
			} else {
				index = -1;
			}
		}

		//更新creditManager的入职时间和在职状态

		logger.info("*******************[SERVICE]信贷经理放款清单同步数据结束，共耗时：[{}]分*******************", stopWatch.elapsedTime()/60000);
	}
	/**
	 * 2016年7月12日
	 */
	public void saveData() {
		StopWatch stopWatch = new StopWatch();
		logger.info("*******************[SERVICE]同步数据开始*******************");
		savePubDatadictionary();
		savePubDditem();
		logger.info("*******************[SERVICE]同步数据结束，共耗时：[{}]分*******************", stopWatch.elapsedTime()/60000);
	}

	public void savePubDatadictionary() {
		StopWatch stopWatch = new StopWatch();
		logger.info("[SERVICE]同步PubDatadictionary开始......");
		try {
			serviceService.loadPubDatadictionary();
			logger.info("[SERVICE]同步PubDatadictionary结束，共耗时：[{}]分", stopWatch.elapsedTime()/60000);
		} catch (Exception e) {
			e.printStackTrace();
			logger.error("[SERVICE]Error：同步PubDatadictionary失败！！！");
		}
	}

	public void savePubDditem() {
		StopWatch stopWatch = new StopWatch();
		logger.info("[SERVICE]同步PubDditem开始......");
		try {
			serviceService.loadDataPubDditem();
			logger.info("[SERVICE]同步PubDditem结束，共耗时：[{}]分", stopWatch.elapsedTime()/60000);
		} catch (Exception e) {
			e.printStackTrace();
			logger.error("[SERVICE]Error：同步PubDditem失败！！！");
		}
	}

//	public void saveHrUnit() {
//		StopWatch stopWatch = new StopWatch();
//		logger.info("[SERVICE]同步HrUnit开始......");
//		try {
//			serviceService.loadData("SELECT * from eadb.hr_units order by unitno", HrUnit.class, "delete from hr_units");
//			logger.info("[SERVICE]同步HrUnit结束，共耗时：[{}]分", stopWatch.elapsedTime()/60000);
//		} catch (Exception e) {
//			e.printStackTrace();
//			logger.error("[SERVICE]Error：同步HrUnit失败！！！");
//		}
//	}
//
//	public void saveHrEmployee() {
//		StopWatch stopWatch = new StopWatch();
//		logger.info("[SERVICE]同步HrEmployee开始......");
//		try {
//			serviceService.loadData("SELECT * from eadb.hr_employee e WHERE e.EMPLOYEESTATUS!='DMS'  order by employeeno", HrEmployee.class, "delete from hr_employee");
//			logger.info("[SERVICE]同步HrEmployee结束，共耗时：[{}]分", stopWatch.elapsedTime()/60000);
//		} catch (Exception e) {
//			e.printStackTrace();
//			logger.error("[SERVICE]Error：同步HrEmployee信息失败！！！");
//		}
//	}

	/**
	 * 同步SD数据
	 */
//	public void saveEabaCustomer() {
//		StopWatch stopWatch = new StopWatch();
//		logger.info("[SERVICE]同步SD开始......");
//		try {
//			//serviceService.loadData("SELECT * from eadb.eaba_customer ORDER BY cno", EabaCustomer.class, "delete from eaba_customer");
//			logger.info("[SERVICE]同步SD结束，共耗时：[{}]分", stopWatch.elapsedTime()/60000);
//		} catch (Exception e) {
//			e.printStackTrace();
//			logger.error("[SERVICE]Error：同步SD信息失败！！！");
//		}
//	}
	
	
	
	
	
	public void loadWToS() {
		StopWatch sw = new StopWatch();
		logger.info("开始同步workflow的信贷经理同步到service");
		try {
			String delSql = " delete from o2o_province_platform ";
			String selectSql = "select aor.id, ao.unit_no,ao.unit_code,ao.org_full_name,aor.user_no,aor.user_name from act_org_role_user aor inner join act_org ao on aor.org_id  =ao.id where aor.role_id ='lletjcsdd4evd' and aor.is_remove ='0'  ";
			String insertSql = " insert into o2o_province_platform(id,ltd,ltd_code,ltd_name,oprate_code,oprate_name)  values (?,?,?,?,?,?)";
			String selectSql1 = "select  r.province_code, r.province_name, p.ltd from o2o_province_platform p left join o2o_platform_regional r on p.ltd =r.platform_id ";
			String updateSql = "update o2o_province_platform set province_code = ? , province_name = ?  where ltd= ? ";
			String delSql1 = "delete from o2o_province_platform where province_code is null ";// 排除信贷经理落在平台公司的数据
			loadWToS(delSql, selectSql, insertSql, selectSql1, updateSql, delSql1);
		} catch (Exception e) {
			logger.error("同步workflow的信贷经理同步到service发生异常", e);
		}
		logger.info("结束同步workflow的信贷经理同步到service，共耗时：[{}]分", sw.elapsedTime() / 1000);
	}
	
	
	/**
	 * @Title: loadWToS
	 * @Description: 从workflow同步数据到service（平台信贷经理）
	 * @author lwx
	 * @param delSql
	 * @param selectSql
	 * @param insertSql
	 * @param updateSql
	 * @param delSql1
	 * @return void
	 * @throws @date
	 *  2017年02月16日 下午10:27:58
	 */
	public void loadWToS(String delSql, String selectSql, String insertSql,String selectSql1,String updateSql,String delSql1) {
		
		try {
			this.serviceDao.excuteSql(delSql);
		} catch (Exception e) {
			e.printStackTrace();
			logger.error("删除service库中的信贷经理时，发生数据库异常", e);
		}
		
		int index = 0;
		while (index >= 0) {
			SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<>(Object[].class, Constant.PROCESS_DATA_LIMIT, index,selectSql);
			PageResult<Object[]> pageResult =serviceDao.findDataWithPageForWorkflow(sqlPageQuery);
			if (pageResult.getReturnDataSize() == 0)
				return;
			
			for (int i = 0; i < pageResult.getReturnDataSize(); i++) {
				Object[] objs = pageResult.getData().get(i);

				try {
					serviceDao.excuteSql(insertSql, objs);
				} catch (Exception e) {
					e.printStackTrace();
					logger.error("向service库中插入信贷经理事，发生数据库异常", e);

				}
			}

			if (pageResult.getPageTotal() > index + 1) {
				index++;
			} else {
				index = -1;
			}
		}
		
		
		
		
		index = 0;
		while (index >= 0) {
			SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<>(Object[].class, Constant.PROCESS_DATA_LIMIT, index,selectSql1);
			PageResult<Object[]> pageResult = this.serviceDao.findDataWithPageForService(sqlPageQuery);
			if (pageResult.getReturnDataSize() == 0)
				return;
			
			for (int i = 0; i < pageResult.getReturnDataSize(); i++) {
				Object[] objs = pageResult.getData().get(i);

				try {
					serviceDao.excuteSql(updateSql, objs);
				} catch (Exception e) {
					e.printStackTrace();
					logger.error("向service库中插入信贷经理事，发生数据库异常", e);

				}
			}

			if (pageResult.getPageTotal() > index + 1) {
				index++;
			} else {
				index = -1;
			}
		}
		
		
		try {
			serviceDao.excuteSql(delSql1);
		} catch (Exception e) {
			e.printStackTrace();
			logger.error("在service库中排除信贷经理落在平台公司的数据，发生数据库异常", e);

		}
		

	}

	
	
	/**
	 * @Title: updateCreditStatus
	 * @Description: 修改征信失效状态
	 * @author 张庆奇
	 * @return void
	 * @throws
	 * @date 2017年3月1日 上午9:18:35
	 */
	public void updateCreditStatus(){
		logger.info("更新修改征信失效状态数据开始......");
		
        String sql="SELECT ly.loanapply_no FROM o2o_loanapply ly LEFT JOIN wf_type_status wf ON ly.loanapply_no = wf.loanapply_no WHERE ly.wf_type = 'ZF' AND wf.wf_type = 'ZX' AND TO_DAYS(now()) - TO_DAYS(wf.end_date) >= 60";
        String exeSql="update o2o_loanapply set credit_status=0 , wf_type='T' ,end_date=now() where loanapply_no=?" ;
     try {
			serviceService.updateCreditData(sql, exeSql);
		} catch (Exception e) {
			e.printStackTrace();
			logger.error("更新修改征信失效状态数据失败！！！");
		}
		logger.info("更新修改征信失效状态数据结束......");
     
	}

	
	/**
	 * 从workflow同步数据
	 */
	public void saveO2oPlatformRegional2() {
		ModelResult<List<PlatformRegional>> modelResult = null;
		try {
			modelResult = sysOrgService.getPlatformRegionalList();
		} catch (Exception e) {
			e.printStackTrace();
			logger.error("Error：用户中心平台大区数据从【Workflow】查询失败！！！,Exception:[{}]", e);
			return;
		}
		String delSql = "delete from o2o_platform_regional";
		// 清空数据中心、Service、WorkFlow的省区表
		baseDao.excuteSql(delSql);
		serviceDao.excuteSql(delSql);
		workFlowDao.execute(delSql);
		// 同步数据中心和Service的省区表
		if (modelResult.isSuccess()) {
			List<PlatformRegional> platformRegionals = modelResult.getModel();
			for (PlatformRegional pr : platformRegionals) {
				O2oPlatformRegionalEntity entity = new O2oPlatformRegionalEntity();
				entity.setPlatformId(pr.getPlatFormId());
				entity.setPlatformCode(pr.getPlatFormCode());
				entity.setPlatformName(pr.getPlatFormName());
				entity.setProvinceCode(pr.getProvinceCode());
				entity.setProvinceName(pr.getProvinceName());
				entity.setRegionalName(pr.getRegionalName());
				entity.setGroupName(pr.getGroupName());
				entity.setNaturalCityCode(pr.getNatureCity());
				entity.setNaturalProvinceCode(pr.getNatureProvince());
				entity.setLevel(pr.getLevel());
				entity.setCreateTime(new Date());

				logger.info("平台ID{}-平台code{}-平台名称{}-省份code{}-省份{}-大区{}-地理省{}-地级市{}-等级{}", pr.getPlatFormId(),
						pr.getPlatFormCode(), pr.getPlatFormName(), pr.getProvinceCode(), pr.getProvinceName(),
						pr.getRegionalName(), pr.getNatureProvince(), pr.getNatureCity(),pr.getLevel());
				baseDao.saveEntity(entity);// 入库到数据中心
				serviceDao.saveEntity(entity); // 入库到Service
				workFlowDao.insert(entity);// 入库workFlow
			}
		}
	}
	
	
	/**
	 * @Title: sychRemouldCustomer
	 * @Description: 从crm同步到service同步客户扩展信息
	 * @author 张庆奇
	 * @return void
	 * @throws
	 * @date 2017年7月13日 上午9:48:06
	 */
	public void sychRemouldCustomer(){
		//第一步:同步 o2o_loan_user 的ID 到 customer_basic_info loan_user_id
		String insertSql="insert into customer_basic_info (loan_user_id)  select  id from o2o_loan_user where sponsor_type is null " ;
		try {
			serviceDao.excuteSql("delete from customer_basic_info ");
		   serviceDao.excuteSql(insertSql);
		} catch (Exception e) {
			e.printStackTrace();
			logger.error("向service库-customer_basic_info表中-插入主键，发生数据库异常", e);

		}
		//第二步:循环loan_id,更新customer_basic_info表其他字段信息
		String selectSql="select loan_id,loan_user_id from customer_basic_info basic left join o2o_loan_user  user on basic.loan_user_id=user.id " ;
		int index = 0;
		while (index >= 0) {
			SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<>(Object[].class, Constant.PROCESS_DATA_LIMIT, index,selectSql);
			PageResult<Object[]> pageResult =serviceDao.findDataWithPageForService(sqlPageQuery);
			if (pageResult.getReturnDataSize() == 0)
				return;
			
			for (int i = 0; i < pageResult.getReturnDataSize(); i++) {
				Object[] objs = pageResult.getData().get(i);
				StringBuffer crmSql=new StringBuffer()  ;
				crmSql.append(" select age ,postcode,work_unit,wechat, marital_status, c.borrower_census_register  ,e.account_city,");
				crmSql.append(" e.account_county,e.account_province,e.permanent_residence,e.issuing_authority,e.license_expiration_date,");
				crmSql.append(" e.education,e.home_phone,e.recipient_county,e.recipient_city,e.recipient_province,e.recipient_address,c.customer_source,");
				crmSql.append(" '' as live_years  ,case when e.children_num=1 then '没有' when e.children_num=2 then '一个'  ");
				crmSql.append(" when e.children_num=3 then '两个' when e.children_num=4 then '两个以上' end  as children_info,");
				crmSql.append(" '' as other_info_desc,c.creator,c.modifier,c.create_date,c.modify_date,");
				crmSql.append(" c.create_id,c.modify_id,2 as data_source");
				crmSql.append(" from customer_basic_info c  left join customer_expansion_info e on c.customer_id=e.customer_id");
				crmSql.append(" where c.customer_id=?");
				try {
					
					RemouldCustomerBasicInfo rb=crmDao.getEntityForCrm(crmSql.toString(),RemouldCustomerBasicInfo.class,objs[0]);
					if(rb!=null){
						RemouldCustomerBasicInfo oriBasic=serviceDao.findEntity("select loan_user_id from customer_basic_info where loan_user_id=?",RemouldCustomerBasicInfo.class,objs[1]);
						BeanUtils.copyProperties(rb, oriBasic);  
						oriBasic.setLoanUserId((String)objs[1]);
						serviceDao.updateEntity(oriBasic);
					}
				} catch (Exception e) {
					e.printStackTrace();
					logger.error("向service库-customer_basic_info表中-更新其他信息，发生数据库异常", e);

				}
			}
			logger.info("同步客户扩展信息数据任务开始......index={}",index);
			if (pageResult.getPageTotal() > index + 1) {
				index++;
			} else {
				index = -1;
			}
		}
		
		
		
	}
	
	/**向service库-eaba_customer_new表中插入数据
	 * 
	 */
	public void sych_eaba_customer_new(){
		try {
			serviceDao.excuteSql("delete from eaba_customer_new ");
		} catch (Exception e) {
			e.printStackTrace();
			logger.error("删除品牌贷sd数据，发生数据库异常", e);

		}
		String sql="select * from eaba_customer_new" ;
		int index = 0;
		while (index >= 0) {
			SqlPageQuery<EabaCustomerNew> sqlPageQuery = new SqlPageQuery<>(EabaCustomerNew.class, Constant.PROCESS_DATA_LIMIT, index,sql);
			PageResult<EabaCustomerNew> pageResult =crmDao.findDataWithPageForMysql(sqlPageQuery);
			if (pageResult.getReturnDataSize() == 0)
				return;
			
			for (int i = 0; i < pageResult.getReturnDataSize(); i++) {
				EabaCustomerNew ea = pageResult.getData().get(i);
				try {
					
					    ea.setSourceType(2);
						serviceDao.saveEntity(ea);
					
				} catch (Exception e) {
					e.printStackTrace();
					logger.error("向service库-eaba_customer_new表中插入数据，发生数据库异常", e);

				}
			}

			if (pageResult.getPageTotal() > index + 1) {
				index++;
			} else {
				index = -1;
			}
		}
		
		
		
		
	}
	
	
	/**
	 * @Title: cancelLoanapply
	 * @Description:贷款申请超时作废
	 * @author 张庆奇
	 * @return void
	 * @throws
	 * @date 2017年8月15日 上午10:44:06
	 */
	public void cancelLoanapply(){
		logger.info("贷款申请超时作废开始......");

		String sql = "select * from (SELECT ly.loanapply_no,ly.wf_type,date_format(ly.submit_date,'%Y-%m-%d %T') as submit_date FROM o2o_loanapply ly , wf_type_status wf where  ly.loanapply_no = wf.loanapply_no and ly.loan_flg='N' and ly.wf_type not in( 'T')  AND wf.wf_type = 'ZX' and TO_DAYS(now()) - TO_DAYS(ly.submit_date) >= 60 ORDER BY ly.submit_date ) tt";
		try {
			serviceService.cancelLoanapply(sql);
		} catch (Exception e) {
			e.printStackTrace();
			logger.error("贷款申请超时作废失败！！！");
		}
		logger.info("贷款申请超时作废结束......");

	}
	
	public void cancelBHLoanapply(){
		logger.info("贷款驳回超时5工作日作废开始......");
		String wfsql="SELECT y.workday from wf_workday y WHERE y.workday <curdate() ORDER BY y.workday DESC LIMIT 4,1";
		String workday= workFlowDao.findString(wfsql);
		logger.info("贷款驳回超时5工作日={}",workday);
		if (!StringUtil.isEmpty(workday)) {
			int pageIndex = 0;
			int pageSize=50;
			int total = 0;
			StringBuffer o2osql=new StringBuffer();
			o2osql.append(" SELECT  y.* from wf_type_status s , o2o_loanapply y  ");
			o2osql.append(" where s.loanapply_no=y.loanapply_no and y.wf_type!='T' and y.loan_flg!='Y' ");
			o2osql.append(" and s.wf_type in ('ZX','NS','BRT') and s.wf_status='BH' and s.wf_date<?");
			while (true) {
				SqlPageQuery<O2oLoanapplyEntity> sqlPageQuery = new SqlPageQuery<>(O2oLoanapplyEntity.class, pageSize,
						pageIndex, o2osql.toString());
				sqlPageQuery.addParam(workday);
				PageResult<O2oLoanapplyEntity> results =jdbcSessionForService.findDataWithPage(sqlPageQuery);
				if (results.getReturnDataSize() == 0)
					return;
				List<O2oLoanapplyEntity> list = results.getData();
				for (O2oLoanapplyEntity entity : list) {
					logger.info("贷款驳回超时5工作日,作废第{}条，单号={}",++total,entity.getBillCode());
					try {
						com.eascs.admin.common.vo.ModelResult<Boolean> modelResult=iicancelService.ItypeBHCancle(entity);
						if (modelResult.isSuccess()) {
							logger.info("贷款驳回超时5工作日,作废成功!");
						}else {
							logger.info("贷款驳回超时5工作日,作废失败：{}",modelResult.getErrMsg());
						}
					} catch (Exception e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
					
				}
				if(results.getReturnDataSize() <pageSize) break;
			}
		}
	}
	
	
}
